﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for DataProvider
/// </summary>
public class DataProvider
{
    public static string ConnectionString = ConfigurationManager.ConnectionStrings["CSDLConnectionString"].ConnectionString;   
    public static SqlConnection GetConnection()
    {
        return new SqlConnection(ConnectionString);
    }

    public static DataTable Fill(DataTable table, String sql, params Object[] parameters)
    {
        SqlCommand command = DataProvider.CreateCommand(sql, parameters);
        new SqlDataAdapter(command).Fill(table);
        command.Connection.Close();
        return table;
    }

    public static DataTable GetData(String sql, params Object[] parameters)
    {
        return DataProvider.Fill(new DataTable(), sql, parameters);
    }

    public static int ExecuteNonQuery(String sql, params Object[] parameters)
    {
        SqlCommand command = DataProvider.CreateCommand(sql, parameters);

        command.Connection.Open();
        int rows = command.ExecuteNonQuery();
        command.Connection.Close();

        return rows;
    }

    public static object ExecuteScalar(String sql, params Object[] parameters)
    {
        SqlCommand command = DataProvider.CreateCommand(sql, parameters);

        command.Connection.Open();
        object value = command.ExecuteScalar();
        command.Connection.Close();

        return value;
    }

    private static SqlCommand CreateCommand(String sql, params Object[] parameters)
    {
        SqlCommand command = new SqlCommand(sql, DataProvider.GetConnection());
        for (int i = 0; i < parameters.Length; i += 2)
        {
            command.Parameters.AddWithValue(parameters[i].ToString(), parameters[i + 1]);
        }
        return command;
    }
}